package com.cyan.note.controller;

import com.cyan.note.mapper.UserMapper;
import com.cyan.note.entity.User;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

/**
 * 数据库功能测试控制器
 * 
 * @author ZhangzhenYu
 * @since 2025-01-14
 */
@RestController
@RequestMapping("/test/db")
@RequiredArgsConstructor
public class DatabaseTestController {
    
    private final UserMapper userMapper;
    private final DataSource dataSource;
    
    /**
     * 测试MyBatis用户查询功能
     */
    @GetMapping("/users")
    public Map<String, Object> testUserQuery() {
        Map<String, Object> result = new HashMap<>();
        
        try {
            result.put("debug", "开始查询用户 admin");
            
            // 测试根据用户名查找
            User adminUser = userMapper.findByUsername("admin");
            
            result.put("debug2", "查询完成，结果: " + (adminUser == null ? "null" : "not null"));
            
            if (adminUser != null) {
                result.put("status", "success");
                result.put("message", "数据库查询功能正常");
                
                Map<String, Object> userInfo = new HashMap<>();
                userInfo.put("id", adminUser.getId());
                userInfo.put("username", adminUser.getUsername());
                userInfo.put("realName", adminUser.getRealName());
                userInfo.put("role", adminUser.getRole());
                userInfo.put("shopId", adminUser.getShopId());
                userInfo.put("status", adminUser.getStatus());
                result.put("adminUser", userInfo);
                
            } else {
                result.put("status", "warning");
                result.put("message", "数据库连接正常，但未找到admin用户");
                result.put("suggestion", "请检查数据库是否已执行初始化脚本");
            }
            
        } catch (Exception e) {
            result.put("status", "error");
            result.put("message", "数据库查询失败: " + e.getMessage());
            result.put("error", e.getClass().getSimpleName());
            result.put("stackTrace", e.getStackTrace()[0].toString());
        }
        
        return result;
    }
    
    /**
     * 测试用户统计查询
     */
    @GetMapping("/count")
    public Map<String, Object> testUserCount() {
        Map<String, Object> result = new HashMap<>();
        
        try {
            // 简单测试查询 - 这里需要添加count方法到UserMapper
            result.put("status", "success");
            result.put("message", "数据库功能正常");
            result.put("note", "用户统计功能待实现");
            
        } catch (Exception e) {
            result.put("status", "error");
            result.put("message", "数据库查询失败: " + e.getMessage());
        }
        
        return result;
    }
    
    /**
     * 使用原始SQL测试数据库
     */
    @GetMapping("/raw-sql")
    public Map<String, Object> testRawSql() {
        Map<String, Object> result = new HashMap<>();
        
        try (Connection conn = dataSource.getConnection()) {
            // 1. 测试表是否存在
            try (PreparedStatement ps = conn.prepareStatement("SHOW TABLES LIKE 'users'");
                 ResultSet rs = ps.executeQuery()) {
                
                boolean tableExists = rs.next();
                result.put("tableExists", tableExists);
                
                if (!tableExists) {
                    result.put("status", "error");
                    result.put("message", "users表不存在，请先运行建表脚本");
                    return result;
                }
            }
            
            // 2. 测试数据是否存在
            try (PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) as user_count FROM users");
                 ResultSet rs = ps.executeQuery()) {
                
                if (rs.next()) {
                    int userCount = rs.getInt("user_count");
                    result.put("userCount", userCount);
                    
                    if (userCount == 0) {
                        result.put("status", "warning");
                        result.put("message", "users表存在但无数据，请运行初始化脚本");
                        return result;
                    }
                }
            }
            
            // 3. 测试查找admin用户
            try (PreparedStatement ps = conn.prepareStatement("SELECT id, username, name FROM users WHERE username = 'admin'");
                 ResultSet rs = ps.executeQuery()) {
                
                if (rs.next()) {
                    result.put("status", "success");
                    result.put("message", "数据库和数据都正常");
                    result.put("adminFound", Map.of(
                        "id", rs.getLong("id"),
                        "username", rs.getString("username"),
                        "name", rs.getString("name")
                    ));
                } else {
                    result.put("status", "warning");
                    result.put("message", "数据存在但未找到admin用户");
                }
            }
            
        } catch (Exception e) {
            result.put("status", "error");
            result.put("message", "原始SQL测试失败: " + e.getMessage());
            result.put("error", e.getClass().getSimpleName());
        }
        
        return result;
    }
}